package com.util;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class XlsMain {

    /**
     * 解析Excel文件，将结果返回
     * @return
     */
    public static List<List<Map<String,Object>>> readXls(MultipartFile file) throws Exception{
//    public static List<List<Map<String,Object>>> readXls(HSSFWorkbook hssfWorkbook) throws Exception{
        List<List<Map<String,Object>>> result = new ArrayList<>();
        if (file == null || file.isEmpty()) {
            return null;
        }
        InputStream is = file.getInputStream();
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
        for(int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++){
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt( numSheet);
            if(hssfSheet == null){
                continue;
            }
            //获取第一行的key值
            HSSFRow keyRow = hssfSheet.getRow(0);
            if(keyRow == null){
                continue;
            }
            List<String> keyList = new ArrayList<>();
            for(int cellNum = 0; cellNum < keyRow.getLastCellNum(); cellNum++){
                HSSFCell keyCell = keyRow.getCell( cellNum);
                keyList.add(keyCell.getStringCellValue());
            }

            List<Map<String,Object>> sheetList = new ArrayList<>();
            // 循环行Row
            for(int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++){
                HSSFRow hssfRow = hssfSheet.getRow( rowNum);
                if(hssfRow == null){
                    continue;
                }
                Map<String,Object> rowMap = new HashMap<>();
                // 循环列Cell
                for(int cellNum = 0; cellNum < keyList.size(); cellNum++){
                    HSSFCell hssfCell = hssfRow.getCell( cellNum);
                    if(hssfCell == null){
                        rowMap.put(keyList.get(cellNum),"");
                        continue;
                    }
                    rowMap.put(keyList.get(cellNum),getValue(hssfCell));
                }
                sheetList.add(rowMap);
            }
            result.add(sheetList);
        }
        return  result;
    }

    @SuppressWarnings("static-access")
    private static String getValue(HSSFCell hssfCell){
        if(hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN){
            return String.valueOf( hssfCell.getBooleanCellValue());
        }else if(hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC){
            return String.valueOf( hssfCell.getNumericCellValue());
        }else{
            return String.valueOf( hssfCell.getStringCellValue());
        }
    }
    /**
     * 文件下载
     * fileName下载文件的名称
     * downLoadPath下载文件的地址
     */
    public static void download(HttpServletRequest req, HttpServletResponse res, String fileName, String downLoadPath){
        res.setContentType("text/html;charset=utf-8");
        res.setCharacterEncoding("UTF-8");
        BufferedInputStream bis = null;
        BufferedOutputStream bos = null;
        try {
            String fn = transcoding(req, fileName);
            long fileLength = new File(downLoadPath).length();
            System.out.println("aaaaaaaaa:"+downLoadPath);
            res.setContentType("application/x-msdownload;");
            res.setHeader("Content-disposition", "attachment; filename="+ fn);
            res.setHeader("Content-Length", String.valueOf(fileLength));
            InputStream is=new FileInputStream(downLoadPath);
            bis = new BufferedInputStream(is);
            bos = new BufferedOutputStream(res.getOutputStream());
            byte[] buff = new byte[2048];
            int bytesRead;
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (bis != null){
                    bis.close();
                }
                if (bos != null){
                    bos.close();
                }
            } catch (Exception e2) {
            }
        }
    }
    //下载是判断pc端是什么浏览器，以解决下载文件名为乱码的问题
    public static String transcoding(HttpServletRequest req, String filename) {
        String fn = filename;
        try {
            String userAgent = req.getHeader("user-agent");
            fn = new String(filename.getBytes("utf-8"), "ISO8859-1");
            if (userAgent != null) {
                userAgent = userAgent.toLowerCase();
                if (userAgent.indexOf("rv:11") != -1) {
                    fn = java.net.URLEncoder.encode(filename, "UTF-8");
                } else if (userAgent.indexOf("msie") != -1) {
                    fn = java.net.URLEncoder.encode(filename, "UTF-8");
                }
            }
        } catch (Exception e) {
        }
        return fn;
    }
    public static void main(String[] args) {
//        try {
//            HSSFWorkbook work = new HSSFWorkbook(new FileInputStream("D:/Book2.xls"));// 得到这个excel表格对象
//            System.out.println(readXls(work).toString());
//        }catch (Exception e){
//            e.printStackTrace();
//        }
    }
}


